<?php

class StudentTransfer_MovenewController extends Zend_Controller_Action
{

    private $_teacherSn;
    public function init()
    {
        $this->_teacherSn = Teacher_Model_Teacher::getSnById($this->view->user->username);
    }

    public function indexAction()
    {
        $form = new StudentTransfer_Form_Movenew();
        $this->view->form = $form;
    }

    public function movenewSetAction()
    {
        $form = new StudentTransfer_Form_Movenew();;
        $form->getElement('year')->setOptions(array('required'=>false));
        $form->getElement('semester')->setOptions(array('required'=>false));
        $res = $this->getRequest()->getParams();
        if ($form->isValid($res)) {
            $row = $res['movenew'];
            $year = $row['selYear'];
            $semester =  1 ; // 預設記錄在第一學期
            $teacherSn = Teacher_Model_Teacher::getSnById();
            // 找出非轉入生資料
            $q = Doctrine_Query::create()
            ->select('a.sn')
            ->from('Student_Model_Student a')
            ->where('a.enroll_year=?', $year)
            ->andwhere('a.sn  NOT IN  (SELECT b.student_sn FROM  Student_Model_StudentTransfer b WHERE  b.causation_kind = ?)',
            Student_Model_StudentTransfer::MOVE_IN);
            $res = $q->fetchArray();
            foreach($res as $val) {
                $studentTransfer = new Student_Model_StudentTransfer();
                $st = $studentTransfer->getTable()->findBySql("year=? AND student_sn=?",array($year,$val['sn']))->getFirst();
                // 修改
                if ($st){
                    $st->date = $row['date'];
                    $st->approve_organ = $row['approve_organ'] ;
                    $st->approve_date  = $row['approve_date'];
                    $st->approve_word = $row['approve_word'];
                    $st->approve_number = $row['approve_number'];
                    $st->teacher_sn = $teacherSn;
                    $st->save();
                }
                else {
                    $studentTransfer->year = $year;
                    $studentTransfer->semester = $semester;
                    $studentTransfer->date = $row['date'];
                    $studentTransfer->approve_organ = $row['approve_organ'] ;
                    $studentTransfer->approve_date  = $row['approve_date'];
                    $studentTransfer->approve_word = $row['approve_word'];
                    $studentTransfer->approve_number = $row['approve_number'];
                    $studentTransfer->teacher_sn = $teacherSn;
                    $studentTransfer->student_sn = $val['sn'];
                    $studentTransfer->causation_kind = Student_Model_StudentTransfer::NEW_STUDENT;
                    $studentTransfer->save();
                }

            }
            $this->_helper->json(1);
        }
        else
        $this->_helper->json(array('error'=>$form->getMessages()));
        // TODO
        // 學年度每一個學生異動記錄
        // 由 student_semester 中查詢學年度學生
        // 或由學生入學年查詢

    }

    /**
     * 新生入學以年度列表
     * @return unknown_type
     */
    public function getNewStudentCountListAction()
    {
        $this->getHelper('layout')->disableLayout();
        $q = Doctrine_Query::create()
        ->select('sn, COUNT(*) AS number,  year, date, approve_date, approve_organ,approve_word,approve_number')
        ->from('Student_Model_StudentTransfer')
        ->where('causation_kind=?', Student_Model_StudentTransfer::NEW_STUDENT)
        ->groupby('year')
        ->orderBy('year DESC');
        $res = $q->fetchArray();
        $this->view->data = $res;
    }

    /**
     *  取出異動資料
     */
    public function getTransferDataByidAction()
    {
        $sn = (int)$this->getRequest()->getParam('sn');
        $core = new StudentTransfer_Model_Core();
        $res = $core->getTransferDataByid($sn);
        $this->_helper->json($res);
    }

    /**
     * 新生男女人數統計
     * @return unknown_type
     */
    public function getNewStudentCountAction()
    {
        // 扣除轉入生
        $year = (int)$this->getRequest()->getParam('year');
        $q = Doctrine_Query::create()
        ->select('a.sex,COUNT(*) AS cc')
        ->from('Student_Model_Student a')
        ->where('a.enroll_year=?', $year)
        ->andwhere('a.sn  NOT IN  (SELECT b.student_sn FROM  Student_Model_StudentTransfer b WHERE  b.causation_kind = ?)',
        Student_Model_StudentTransfer::MOVE_IN)
        ->groupBy('a.sex');

        $res = $q->fetchArray();
        $arr = array();
        //   echo $q->getSqlQuery();
        foreach($res as $row) {
            $arr[$row['sex']] = $row['cc'];
            $arr['count'] += $row['cc'];
        }
        $this->_helper->json($arr);
    }
}

